package tech.yqyl.store;

import cn.hutool.core.text.StrFormatter;
import tech.yqyl.StringUtil;
import tech.yqyl.solo.Article;
import tech.yqyl.solo.ArticleSeries;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

class Mysql2FileArticleStore {

    private static final String URL = "jdbc:mysql://localhost:3306/solo?useSSL=false"; // 数据库URL
    private static final String USER = "root"; // 数据库用户名
    private static final String PASSWORD = "password"; // 数据库密码
    private static final String importByTag = "精通MySQL系列";
    private static final String categories = "默认分类";

    public static void generate() {
        List<Article> blogs = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // 1. 注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. 打开连接
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            // 3. 创建语句对象
            statement = connection.createStatement();
            // 4. 执行查询
            String sql = "select * from b3_solo_article where articleTags in (select d.articleTags from (SELECT articleTags, COUNT(*) AS counts\n" +
                    "FROM b3_solo_article " +
                    (StringUtil.isEmpty(importByTag) ? "" : "where articleTags in (\"{}\")\n") +
                    "GROUP BY articleTags\n" +
                    "HAVING counts >= 3) as d)"; // SQL查询语句
            resultSet = statement.executeQuery(StrFormatter.format(sql, importByTag ));

            // 5. 处理结果集并转换为User对象
            while (resultSet.next()) {
                Article blog = new Article();
                blog.setOId(resultSet.getString("oId"));
                blog.setArticleTitle(resultSet.getString("articleTitle"));
                blog.setArticleAbstract(resultSet.getString("articleAbstract"));
                blog.setArticleAbstractText(resultSet.getString("articleAbstractText"));
                blog.setArticleTags(resultSet.getString("articleTags"));
                blog.setArticleAuthorId(resultSet.getString("articleAuthorId"));
                blog.setArticleContent(resultSet.getString("articleContent"));
                blog.setArticlePermalink(resultSet.getString("articlePermalink"));
                blog.setArticlePutTop(resultSet.getString("articlePutTop"));
                blog.setArticleCreated(resultSet.getLong("articleCreated"));
                blog.setArticleUpdated(resultSet.getLong("articleUpdated"));
                blog.setArticleRandomDouble(resultSet.getString("articleRandomDouble"));
                blog.setArticleSignId(resultSet.getString("articleSignId"));
                blog.setArticleViewPwd(resultSet.getString("articleViewPwd"));
                blog.setArticleImg1URL(resultSet.getString("articleImg1URL"));
                blog.setArticleStatus(resultSet.getInt("articleStatus"));
                blogs.add(blog);
            }
            System.err.println("总行数："+blogs.size());
            Map<String, ArticleSeries> articleSeries = new HashMap<>();
            for(Article article : blogs ) {
                articleSeries.computeIfAbsent(article.getFirstTag(), tag-> new ArticleSeries(article.getFirstTag(), StrFormatter.format("/newcloud/blog-document/blog{}", sql.contains("not in")?"/待分组":"")))
                        .stack(categories, article);
            }
            articleSeries.values().parallelStream().forEach(series->{
                // 存储 系列 分组 文章
                series.store();
            });
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
